package com.psl.services.work.sell;

import com.psl.Dao.Conn;
import com.psl.model.selllist;
import org.apache.struts2.ServletActionContext;

import javax.servlet.http.HttpServletRequest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SellServices {
    public List All() {
        Connection conn = new Conn().getConn();
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        List Allselllist = new ArrayList();
        String sql = "SELECT id,h_no,h_name,h_size,h_r,h_n,h_num,h_unit,h_senddate,h_ticketno,usepackage,remark,c_name from selllist GROUP BY id ORDER by id DESC ";
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                selllist p = new selllist();
                p.setId(rs.getInt(1));
                p.setH_no(rs.getString(2));
                p.setH_name(rs.getString(3));
                p.setH_size(rs.getString(4));
                p.setH_r(rs.getString(5));
                p.setH_n(rs.getString(6));
                p.setH_num(rs.getInt(7));
                p.setH_unit(rs.getString(8));
                p.setH_senddate(rs.getString(9));
                p.setH_ticketno(rs.getString(10));
                p.setUsepackage(rs.getString(11));
                p.setRemark(rs.getString(12));
                p.setC_name(rs.getString(13));
                Allselllist.add(p);
                HttpServletRequest request= ServletActionContext.getRequest();
                request.setAttribute("Allselllist",Allselllist);
            }
            return Allselllist;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        }
    }


    public List print(String h_ticketno) {
        Connection conn = new Conn().getConn();
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        List print1 = new ArrayList();
        try {
            pstmt = conn.prepareStatement("SELECT id,h_no,h_name,h_size,h_r,h_n,h_num,h_unit,h_senddate,h_ticketno,usepackage,remark,c_name from selllist where h_ticketno=?");
            pstmt.setString(1, h_ticketno);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                selllist p = new selllist();
                p.setId(rs.getInt(1));
                p.setH_no(rs.getString(2));
                p.setH_name(rs.getString(3));
                p.setH_size(rs.getString(4));
                p.setH_r(rs.getString(5));
                p.setH_n(rs.getString(6));
                p.setH_num(rs.getInt(7));
                p.setH_unit(rs.getString(8));
                p.setH_senddate(rs.getString(9));
                p.setH_ticketno(rs.getString(10));
                p.setUsepackage(rs.getString(11));
                p.setRemark(rs.getString(12));
                p.setC_name(rs.getString(13));
                print1.add(p);
                HttpServletRequest request= ServletActionContext.getRequest();
                request.setAttribute("print1",print1);
            }
            return print1;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public List print2(String h_ticketno) {
        Connection conn = new Conn().getConn();
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        List print2 = new ArrayList();
        try {
            pstmt = conn.prepareStatement("SELECT DISTINCT h_senddate,h_ticketno,c_name from selllist where h_ticketno=?");
            pstmt.setString(1, h_ticketno);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                selllist p = new selllist();
                p.setH_senddate(rs.getString(1));
                p.setH_ticketno(rs.getString(2));
                p.setC_name(rs.getString(3));
                print2.add(p);
                HttpServletRequest request= ServletActionContext.getRequest();
                request.setAttribute("print2",print2);
            }
            return print2;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }




    public boolean InsertSellList(String h_no,String h_name,String h_size,String h_r,String h_n,int h_num,String h_unit,String h_senddate,String h_ticketno,String usepackage,String remark,String c_name) {
        Connection conn = new Conn().getConn();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("insert into selllist(h_no,h_name,h_size,h_r,h_n,h_num,h_unit,h_senddate,h_ticketno,usepackage,remark,c_name) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
            pstmt.setString(1, h_no);
            pstmt.setString(2, h_name);
            pstmt.setString(3, h_size);
            pstmt.setString(4, h_r);
            pstmt.setString(5, h_n);
            pstmt.setInt(6, h_num);
            pstmt.setString(7, h_unit);
            pstmt.setString(8, h_senddate);
            pstmt.setString(9, h_ticketno);
            pstmt.setString(10, usepackage);
            pstmt.setString(11, remark);
            pstmt.setString(12, c_name);
            pstmt.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public boolean sell(String h_no,String h_size,String h_r,String h_n,int h_num) {
        Connection conn = new Conn().getConn();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("call sell(?,?,?,?,?)");
            pstmt.setString(1, h_no);
            pstmt.setString(2, h_size);
            pstmt.setString(3, h_r);
            pstmt.setString(4, h_n);
            pstmt.setInt(5, h_num);
            pstmt.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }






    public boolean updateprocstatus(String h_ticketno)
    {
        Connection conn = new Conn().getConn();
        PreparedStatement pstmt = null;
        try
        {
            pstmt = conn.prepareStatement("UPDATE selllist set h_ticketno=? where h_ticketno=1");
            pstmt.setString(1, h_ticketno);
            pstmt.executeUpdate();
            return true;
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return true;
    }


    public boolean del(int id)
    {
        Connection conn = new Conn().getConn();
        PreparedStatement pstmt = null;
        try
        {
            pstmt = conn.prepareStatement("DELETE from selllist  where id=?");
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
            return true;
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
        return true;
    }
}
